package com.yhp.dao.impl;

import com.yhp.bean.Student;
import com.yhp.dao.DBUtils;
import com.yhp.dao.StudentDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getall(int pageindex,int pagesize,String stuname,String stuno,int sex) {
        List list=new ArrayList();
        try {
            //String sql="select * from student limit ?,?";
            //每页显示5条 pagesize=5 pageindex=1,2,3,4,5
            //limit 0,5
            //limit 5,5
            //limit 10,5
            //limit (pageindex-1)*pagesize,pagesize
            StringBuffer sql=new StringBuffer("select * from student where 1=1 ");
            if (stuname!=null&&stuname.length()>0){
                sql.append(" and stuname like ? ");
            }
            if (stuno!=null&&stuno.length()>0){
                sql.append(" and stuno=? ");
            }
            if (sex!=-1){
                sql.append(" and sex=? ");
            }
            sql.append(" limit ?,?");
            List params=new ArrayList();
            if (stuname!=null&&stuname.length()>0){
                params.add("%"+stuname+"%");
            }
            if (stuno!=null&&stuno.length()>0){
                params.add(stuno);
            }
            if (sex!=-1){
                params.add(sex);
            }
            params.add(((pageindex-1)*pagesize));
            params.add(pagesize);
            resultSet = query(sql.toString(), params);
            while(resultSet.next()){
                Student student=new Student();
                student.setStuno(resultSet.getString("stuno"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setProfession(resultSet.getString("profession"));
                student.setStuid(resultSet.getInt("stuid"));
                student.setGid(resultSet.getInt("gid"));
                student.setStuname(resultSet.getString("stuname"));
                student.setAddress(resultSet.getString("address"));
                student.setSex(resultSet.getInt("sex"));
                student.setEmail(resultSet.getString("email"));
                student.setIdnumber(resultSet.getString("idnumber"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setPhone(resultSet.getString("phone"));
                student.setPolitics(resultSet.getString("politics"));
                list.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    @Override
    public int totalcount(String stuname, String stuno, int sex) {
        int total=0;
        try {
            StringBuffer sql=new StringBuffer("select count(*) from student where 1=1  ");
            if (stuname!=null&&stuname.length()>0){
                sql.append(" and stuname like ? ");
            }
            if (stuno!=null&&stuno.length()>0){
                sql.append(" and stuno=? ");
            }
            if (sex!=-1){
                sql.append(" and sex=? ");
            }
            List params=new ArrayList();
            if (stuname!=null&&stuname.length()>0){
                params.add("%"+stuname+"%");
            }
            if (stuno!=null&&stuno.length()>0){
                params.add(stuno);
            }
            if (sex!=-1){
                params.add(sex);
            }
            //resultSet=query("select count(*) from student",null);
            resultSet = query(sql.toString(),params);
            while(resultSet.next()){
                total=resultSet.getInt(1);//得到第一列
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }
    @Override
    public int insertStu(Student student){
        int update=0;
        try {
            String sql="insert into student(stuno,stuname,gid,sex) values(?,?,?,?)";
            List params=new ArrayList();
            params.add(student.getStuno());
            params.add(student.getStuname());
            params.add(student.getGid());
            params.add(student.getSex());

            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int del(String sid) {
        int k=0;
        try {
            String sql="delete from student where stuno=?";
            List params=new ArrayList();
            params.add(sid);
            k=update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return k;
    }

    @Override
    public Student findbyid(String sid) {
        Student student=new Student();
        try {
            String sql="select * from student where stuno=?";
            List params=new ArrayList();
            params.add(sid);
            resultSet=query(sql,params);
            while(resultSet.next()){
                student.setStuno(resultSet.getString("stuno"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setProfession(resultSet.getString("profession"));
                student.setStuid(resultSet.getInt("stuid"));
                student.setGid(resultSet.getInt("gid"));
                student.setStuname(resultSet.getString("stuname"));
                student.setAddress(resultSet.getString("address"));
                student.setSex(resultSet.getInt("sex"));
                student.setEmail(resultSet.getString("email"));
                student.setIdnumber(resultSet.getString("idnumber"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setPhone(resultSet.getString("phone"));
                student.setPolitics(resultSet.getString("politics"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;


    }

    @Override
    public int updatestu(Student student) {
        int k=0;
        try {
            String sql="update student set gid=?,stuname=? where stuno=?";
            List params=new ArrayList();
            params.add(student.getGid());
            params.add(student.getStuname());
            params.add(student.getStuno());
            k=update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return k;
    }
}
